import connDB
import re


def extract_sql_fields(sql_str):
    # 首先找到CREATE TABLE语句的开始位置
    create_table_pattern = r'CREATE\s+TABLE\s+\w+\s*\('
    match_start = re.search(create_table_pattern, sql_str, re.IGNORECASE)
    if not match_start:
        return []
    start_pos = match_start.end()
    # 使用括号计数找到匹配的结束括号
    paren_count = 1
    current_pos = start_pos
    while paren_count > 0 and current_pos < len(sql_str):
        if sql_str[current_pos] == '(':
            paren_count += 1
        elif sql_str[current_pos] == ')':
            paren_count -= 1
        current_pos += 1
    if paren_count == 0:
        # 找到了匹配的结束括号
        fields_content = sql_str[start_pos:current_pos - 1].strip()
        #print(f"提取到的字段内容长度: {len(fields_content)}")
        # 分割字段定义
        field_defs = []
        current_field = ""
        paren_depth = 0
        for char in fields_content:
            if char == '(':
                paren_depth += 1
            elif char == ')':
                paren_depth -= 1
            elif char == ',' and paren_depth == 0:
                if current_field.strip():
                    field_defs.append(current_field.strip())
                current_field = ""
                continue
            current_field += char
        # 添加最后一个字段
        if current_field.strip():
            field_defs.append(current_field.strip())

        #print(f"分割后的字段数量: {len(field_defs)}")

        field_info = []
        for i, field_def in enumerate(field_defs):
            #print(f"字段 {i + 1}: {field_def}")
            # 匹配字段名（字段名后跟空格和数据类型）
            field_match = re.match(r'^([a-zA-Z_][a-zA-Z0-9_]*)\s+', field_def)
            if field_match:
                field_name = field_match.group(1)
                field_info.append({'name': field_name, 'comment': ''})
            else:
                print(f"无法解析字段: {field_def}")
    else:
        print("未能找到匹配的结束括号")
        return []
    # 提取字段注释
    comment_pattern = r'COMMENT\s+ON\s+COLUMN\s+[\w\.]+\.(\w+)\s+IS\s+\'(.*?)\';\s*'
    comments = re.findall(comment_pattern, sql_str, re.DOTALL | re.IGNORECASE)
    comment_dict = {field: comment.replace("'", "''") for field, comment in comments}
    print(f"提取到的注释数量: {len(comment_dict)}")
    # 关联字段与注释
    for field in field_info:
        if field['name'] in comment_dict:
            field['comment'] = comment_dict[field['name']]

    return field_info

def format_output(fields, tableAs):
    if not fields:
        return ""
    output_lines = []

    # 第一个字段特殊处理
    first_field = fields[0]
    if first_field['comment']:
        output_lines.append(f"{tableAs}{first_field['name']} -- {first_field['comment']}")
    else:
        output_lines.append(f"{tableAs}{first_field['name']}")

    # 后续字段添加逗号
    for field in fields[1:]:
        if field['comment']:
            output_lines.append(f",{tableAs}{field['name']} -- {field['comment']}")
        else:
            output_lines.append(f",{tableAs}{field['name']}")

    return '\n'.join(output_lines)


# 不加注释
def format_output_un_comment(fields, tableAs):
    if not fields:
        return ""
    output_lines = []

    # 第一个字段特殊处理
    first_field = fields[0]
    output_lines.append(f"{tableAs}{first_field['name']}")

    # 后续字段添加逗号
    for field in fields[1:]:
        output_lines.append(f", {tableAs}{field['name']}")

    return '\n'.join(output_lines)


# 创建连接得到建表语句，解析出所有字段，每个字段一行，加上逗号
def getFileds(schema, tableName, tableAs,connConfig):
    # 创建连接
    connDwr = connDB.create_connection(**connConfig)
    if connDwr:
        queryDwrCreateTable = f"select pg_get_tabledef('{schema}.{tableName}');"
        dwrCreateTable = connDB.fetch_data(connDwr, queryDwrCreateTable)
        if dwrCreateTable:
            #print("原始建表语句:")
            #print(dwrCreateTable[0][0])  # 查看完整建表语句
            fields = extract_sql_fields(dwrCreateTable[0][0])
            print(f"解析到的字段数量: {len(fields)}")  # 查看解析到的字段数量
            return format_output(fields, tableAs)
        connDwr.close()
    return ""
# 创建连接得到建表语句，解析出所有字段，每个字段一行，加上逗号
def getFiledsUnComment(schema, tableName, tableAs,connConfig):
    # 创建连接
    connDwr = connDB.create_connection(**connDB.connConfig)
    if connDwr:
        queryDwrCreateTable = f"select pg_get_tabledef('{schema}.{tableName}');"
        dwrCreateTable = connDB.fetch_data(connDwr, queryDwrCreateTable)
        if dwrCreateTable:
            fields = extract_sql_fields(dwrCreateTable[0][0])
            print(f"解析到的字段数量: {len(fields)}")
            return format_output_un_comment(fields, tableAs)
        connDwr.close()
    return ""

if __name__ == "__main__":

    schema = "common_dm"
    tableAs= ""

    #schema = 'dt_yunnan'
    tableName = "dm_ft_inv_oprn_chng_info_d_f"
    #连数据库配置文件
    #connConfig = connDB.dwr_prd_config
    connConfig = connDB.dm_prd_config
    result = getFileds(schema, tableName,tableAs,connConfig)
    #不要注释
    #result = getFiledsUnComment(schema, tableName,tableAs)
    print(result)
